package org.acooke.uykfd.db

import java.sql.Connection
import java.util.regex.Pattern

import org.apache.empire.data.Column
import org.apache.empire.data.RecordData
import org.apache.empire.db.DBColumnExpr
import org.apache.empire.db.DBCommand
import org.apache.empire.db.DBCommandExpr
import org.apache.empire.db.DBReader
import org.apache.empire.db.DBRecord
import org.apache.empire.db.DBRecordData
import org.apache.empire.db.expr.compare.DBCompareExpr

import scala.collection.JavaConversions._

class MissingRecordException(msg: String) extends RuntimeException(msg)

/**
 * Associate a record with a particular table.
 */
trait Table[F, T <: ScalaTable[F]] {

  val table: T

  def where(condition: DBCompareExpr): DBCommand
  def allWhere(condition: DBCompareExpr): DBCommand
  def erase(cnxn:Connection)
}

abstract class SimpleTable[F, T <: ScalaTable[F]](t: => T) {

  lazy val table = t

  def all: DBCommand = {
    val cmd = Schema.createCommand
    cmd.select(table.getColumns)
    cmd
  }

  def where(condition: DBCompareExpr): DBCommand = {
    val cmd = Schema.createCommand
    cmd.where(condition)
    cmd
  }

  def allWhere(condition: DBCompareExpr): DBCommand = {
    val cmd = all
    cmd.where(condition)
    cmd
  }

  def erase(cnxn:Connection) {
    val cmd = Schema.createCommand
    Schema.executeSQL(cmd.getDelete(table), cnxn)
  }

  protected def id(x: Int): Int = x

  protected def buildList[R](
    cmd: DBCommandExpr, key: DBColumnExpr, transform: Int => R, cnxn: Connection
  ): List[R] = {
    val reader = new DBReader()
    var list: List[R] = Nil
    try {
      reader.open(cmd, cnxn)
      while (reader.moveNext) list ::= transform(Record.asInt(reader, key))
      list
    } finally {
      reader.close
    }
  }

}


abstract class Factory[R <: Record[R,F,T], F, T <: ScalaTable[F]](table: => T)
  extends SimpleTable[F, T](table) {

  protected def rowFactory: R

  // allow list comprehensions to use single returns
  implicit def asList[R <: Row](row: R): List[R] = row::Nil

  protected def buildList(cmd: DBCommandExpr, cnxn: Connection, n: Int = -1): List[R] = {
    val reader = new DBReader
    var list: List[R] = Nil
    try {
      reader.open(cmd, cnxn)
      var count = n
      while (reader.moveNext && count != 0) {
        list ::= rowFactory.initFrom(reader)
        count = count - 1
      }
      list
    } finally {
      reader.close
    }
  }

}

trait Row

class Record[S <: Record[S,F,T], F, T <: ScalaTable[F]](t: T) {

  self: S =>

  val table = t

  private val row = new DBRecord
  row.create(table)

  protected[db] def pull(
      cmd: DBCommand, cnxn: Connection, fallback: (Exception => S)=((x) => throw x)): S = {

    val reader = new DBReader
    try {
      reader.getRecordData(cmd, cnxn)
      initFrom(reader)
    } catch {
      case e: Exception => fallback(e)
    } finally {
      reader.close
    }
  }

  protected def asInt(column: DBColumnExpr) = Record.asInt(row, column)

  protected def asString(column: DBColumnExpr) = Record.asString(row, column)

  protected def asDouble(column: DBColumnExpr) = Record.asDouble(row, column)

  protected def set(column: Column, value: Any) = {
    val cleaned = value match {
      case s: String => Record.clean(s)
      case a: Any => a
    }
    row.setValue(column, cleaned)
  }

  protected def push(column: Column, value: Any, cnxn: Connection) = {
    set(column, value)
    update(cnxn)
  }

  protected def update(cnxn: Connection) = row.update(cnxn)

  def initFrom(reader: DBReader): S = {
    reader.initRecord(table, row)
    this
  }
 
}

object Record {

  val ctrl = Pattern.compile("[\\x00-\\x1f]")

  def clean(string: String): String = {
    val matcher = ctrl.matcher(string)
    matcher.replaceAll("")
  }

  def asInt(record: RecordData, column: DBColumnExpr): Int = {
    record.getValue(record.getFieldIndex(column)) match {
      case i: java.lang.Integer => i.intValue
      case l: java.lang.Long => l.intValue
      case null => throw new NullPointerException("Null value for " + column)
      case x => throw new ClassCastException(x.getClass.getName)
    }
  }

  def asString(record: RecordData, column: DBColumnExpr): String = {
    record.getValue(record.getFieldIndex(column)) match {
      case s: java.lang.String => s
      case null => "" // hsqldb confuses nulls and empty strings?!
      case x => throw new ClassCastException(x.getClass.getName)
    }
  }

  def asDouble(record: RecordData, column: DBColumnExpr): Double = {
    record.getValue(record.getFieldIndex(column)) match {
      case i: java.lang.Integer => 0.0 + i.intValue
      case l: java.lang.Long => 0.0 + l.intValue
      case d: java.lang.Double => d.doubleValue
      case x => throw new ClassCastException(x.getClass.getName)
    }
  }

}

/**
 * Support for tables that contain an ID column.
 */
trait IdRow extends Row {

  def id: Int

}

trait IdTable[R <: IdRow, F, T <: Schema.Id[F]]
  extends Table[F,T] {

  def fromId(id: Int, cnxn: Connection): R

}

abstract class IdFactory[R <: IdRecord[R,F,T], F, T <: Schema.Id[F]](table: => T)
  extends Factory[R,F,T](table) {

  def fromId(id: Int, cnxn: Connection) = {
    val cmd = allWhere(table.ID.is(id))
    rowFactory.pull(cmd, cnxn, (x: Exception) => throw x)
  }

}

protected abstract class IdRecord[S <: IdRecord[S,F,T], F, T <: Schema.Id[F]](table: => T)
  extends Record[S,F,T](table) {

  self: S =>

  def id = asInt(table.ID)

  override def hashCode = id

  override def equals(other: Any) = 
    other.isInstanceOf[IdRecord[_,_,_]] && other.asInstanceOf[IdRecord[_,_,_]].id == id

}


/**
 * Support for tables that contain ID and VALUE columns.
 */
trait IdValueRow extends IdRow {

  def value: String
  def pushValue(value: String, cnxn: Connection): Boolean

}

trait IdValueTable[R <: IdValueRow, F, T <: Schema.IdValue[F]]
  extends IdTable[R,F,T] {

  def lookupValue(value: String, cnxn: Connection): Option[R]

}

abstract class IdValueFactory[R <: IdValueRecord[R,F,T], F, T <: Schema.IdValue[F]](table: => T)
  extends IdFactory[R,F,T](table) {

  def fromValue(value: String, cnxn: Connection) = {
    val cleaned = Record.clean(value)
    val cmd = allWhere(table.VALUE.is(cleaned))
    val row = rowFactory
    row.pull(cmd, cnxn, (x: Exception) => {row.pushValue(cleaned, cnxn); row})
  }

  def lookupValue(value: String, cnxn: Connection) = {
    val cmd = allWhere(table.VALUE.is(Record.clean(value)))
    val row = rowFactory
    try {
      Some(row.pull(cmd, cnxn))
    } catch {
      case x: Exception => None
    }
  }

}

protected abstract class IdValueRecord[S <: IdValueRecord[S,F,T], F, T <: Schema.IdValue[F]](table: => T)
  extends IdRecord[S,F,T](table) {

  self: S =>

  def value = asString(table.VALUE)

  def pushValue(value: String, cnxn: Connection) = push(table.VALUE, value, cnxn)

  override def toString = value

}

